/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Reportes;
import java.sql.ResultSet;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;

/**
 *
 * @author usuario
 */
public class generaClientes extends ManejaExcel {

    public generaClientes(String name, String param1, String param2,int opc1, String opc2){
        super(name,param1,param2,opc1,opc2);
        
    }

    
    protected void escribirInfo() {

       try{ 

        WritableSheet s1 = workbook.createSheet("Clientes", 0);
        s1.setColumnView(1,25);
        s1.setColumnView(2,25);
        s1.setColumnView(3,30);
        
       
        //Carga primer fila con los titulos
        Label l1 = new Label(1,0,"Apellido", arial10BoldFormat);
        s1.addCell(l1);
        Label l2 = new Label(2,0,"Nombre", arial10BoldFormat);
        s1.addCell(l2);
        Label l3 = new Label(3,0,"E-Mail", arial10BoldFormat);
        s1.addCell(l3);
         Label l4 = new Label(4,0,"Especialidad", arial10BoldFormat);
        s1.addCell(l4);
         Label l5 = new Label(5,0,"Localidad", arial10BoldFormat);
        s1.addCell(l5);
         Label l6 = new Label(6,0,"Provincia", arial10BoldFormat);
        s1.addCell(l6);
         Label l7 = new Label(7,0,"Monto", arial10BoldFormat);
        s1.addCell(l7);
         Label l8 = new Label(8,0,"Moneda", arial10BoldFormat);
        s1.addCell(l8);
        Number n7;
        //Trae la info indicada
         String consulta="SELECT C.Nombre as Name, Apellido, Mail, Especialidad ,Localidad, Provincia, SUM(Total) as MontoTotal, Simb_M" +
                 " FROM Clients C JOIN Pedids P on (C.Id_Cliente=[PP.Id_cliente]) JOIN Congreso GR on (Iniciales_Congreso=Iniciales and Fecha_Congreso=Fecha_Inicio) "+
                 where()+" GROUP BY C.Nombre,Apellido,Mail,Especialidad,Localidad,Provincia, Simb_M ORDER BY Apellido,C.Nombre";
       // System.out.println(consulta);
         ResultSet resultSet = traeInfo(consulta);
           //Carga todas las filas 
        int i=0;
        while (resultSet.next()){
            i=i+1;
            l1 = new Label(1,i,resultSet.getString("Apellido"), wrappedText);
            s1.addCell(l1);
            l2 = new Label(2,i,resultSet.getString("Name"), wrappedText);
            s1.addCell(l2);
            l3 = new Label(3,i,resultSet.getString("Mail"), wrappedText);
            s1.addCell(l3);
            l4 = new Label(4,i,resultSet.getString("Especialidad"), wrappedText);
            s1.addCell(l4);
            l5 = new Label(5,i,resultSet.getString("Localidad"), wrappedText);
            s1.addCell(l5);
            l6 = new Label(6,i,resultSet.getString("Provincia"), wrappedText);
            s1.addCell(l6);
            n7 = new Number(7,i,resultSet.getDouble("MontoTotal"), wrappedText);
            s1.addCell(n7);
            l8 = new Label(8,i,resultSet.getString("Simb_M"), wrappedText);
            s1.addCell(l8);
        }
            l6 = new Label(6,i+1,"TOTAL:", arial10BoldFormat);
            //s1.addCell(l6);
            Formula f7 = new Formula(7,i+1,"SUM(h2:h"+(i+1)+")",wrappedText);
           // if (i>0) s1.addCell(f7);
         }catch(Exception e){e.printStackTrace();}
    }
    
    protected String where(){
        String val=" ";
        switch(opcion1){
             case 1: val= super.where(); break;
             case 2: val= super.where(); break;
             case 3: val= super.where(); break;
             case 4: val= "where Especialidad like '"+opcion2+"' "; break;
         }
        return val;
    }
    
}
